﻿using System;
using System.Data;
using System.Text;
using System.Collections.Generic;
using MySql.Data.MySqlClient;
using Think.Lenovo.Com.Models;

namespace Think.Lenovo.Com.DataAccess
{
    public class productInfo
    {
        public long Addproduct(productModel pro)
        {
            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;

            //sql语句
            StringBuilder sql = new StringBuilder(128);
            sql.Append("insert into ")
                .Append("tbl_product (title, pclass, pic,Content,bright,releaseTime)")
                .Append("values(@title,@pclass,@pic,@Content,@bright,@releaseTime)");


            MySqlParameter[] ps = new MySqlParameter[] {
                    new MySqlParameter("@title",MySqlDbType.VarChar,500),
                    new MySqlParameter("@pclass",MySqlDbType.VarChar,300),
                    new MySqlParameter("@pic",MySqlDbType.VarChar,500),
                    new MySqlParameter("@Content",MySqlDbType.Text),
                    new MySqlParameter("@bright",MySqlDbType.VarChar,320),
                    new MySqlParameter("@releaseTime",MySqlDbType.DateTime)
                };

            ps[0].Value = pro.Title;
            ps[1].Value = pro.Pclass;
            ps[2].Value = pro.Pic;
            ps[3].Value = pro.Content;
            ps[4].Value = pro.Bright;
            ps[5].Value = pro.ReleaseTime;

            long id = MysqlHelper.Insert(conn, sql.ToString(), ps);
            return id;
        }

        public long Updateproduct(productModel pro)
        {
            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;

            //sql语句
            StringBuilder sql = new StringBuilder(128);
            sql.Append("update tbl_product ")
                .Append("set title=@title, pclass=@pclass, pic=@pic,Content=@Content,bright=@bright,releaseTime=@releaseTime where id=@id");


            MySqlParameter[] ps = new MySqlParameter[] {
                    new MySqlParameter("@id",MySqlDbType.Int32),
                    new MySqlParameter("@title",MySqlDbType.VarChar,500),
                    new MySqlParameter("@pclass",MySqlDbType.Int32),
                    new MySqlParameter("@pic",MySqlDbType.VarChar,500),
                    new MySqlParameter("@Content",MySqlDbType.Text),
                    new MySqlParameter("@bright",MySqlDbType.VarChar,320),
                    new MySqlParameter("@releaseTime",MySqlDbType.DateTime)
                };
            ps[0].Value = pro.ID;
            ps[1].Value = pro.Title;
            ps[2].Value = pro.Pclass;
            ps[3].Value = pro.Pic;
            ps[4].Value = pro.Content;
            ps[5].Value = pro.Bright;
            ps[6].Value = pro.ReleaseTime;

            long id = MysqlHelper.Update(conn, sql.ToString(), ps);
            return id;
        }

        public DataSet GetAllProduct()
        {
            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;
            string sql = "Select * from tbl_product order by id desc";
            DataSet ds = new DataSet();
            ds = MySqlHelper.ExecuteDataset(conn, sql.ToString());
            return ds;
        }

        /// <summary>
        /// 用类别获得产品
        /// </summary>
        /// <returns></returns>
        public DataSet GetAllProductByClass(string id)
        {
            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;
            string sql = "Select * from tbl_product where pclass='" + id + "' order by id desc";
            DataSet ds = new DataSet();
            ds = MySqlHelper.ExecuteDataset(conn, sql.ToString());
            return ds;
        }


        public DataSet GetProductId(int id)
        {
            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;
            string sql = "Select * from tbl_product where id=" + id;
            DataSet ds = new DataSet();
            ds = MySqlHelper.ExecuteDataset(conn, sql.ToString());
            return ds;
        }

        public void DeleteProduct(int id)
        {
            #region //信息

            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;

            #region//sql语句
            string sql = "delete from tbl_product Where Id=@Id";

            MySqlParameter[] ps = new MySqlParameter[] {
                    new MySqlParameter("@id",MySqlDbType.Int32)
            };
            ps[0].Value = id;
            MysqlHelper.Delete(conn, sql.ToString(), ps);
            #endregion
            #endregion
        }

        public DataSet GetProducts(int pageIndex, int pageSize, String conditions, String orderBy)
        {
            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;
            //string sql = "Select * from tbl_product order by id desc";
            StringBuilder sql = new StringBuilder(1024);
            sql.Append("Select * from tbl_product ");
            if (!String.IsNullOrEmpty(conditions))
            {
                sql.Append(" Where")
                    .Append(conditions);
            }
            if (!String.IsNullOrEmpty(orderBy))
            {
                sql.Append(" Order BY" + orderBy);
            }
            DataSet ds = new DataSet();
            ds = MySqlHelper.ExecuteDataset(conn, sql.ToString());
            return ds;
        }

        public int GetProductCount(String conditions)
        {
            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;
            StringBuilder sql = new StringBuilder(128);
            sql.Append("Select Count(Id) As productCount From tbl_product ");
            if (!String.IsNullOrEmpty(conditions))
            {
                sql.Append(conditions);
            }
            Object obj = MysqlHelper.ExecuteScalar(conn, sql.ToString());
            return obj == null ? 0 : Convert.ToInt32(obj);
        }
    }
}
